package com.mihuxiaobaigao.ai.Mapper;

import com.mihuxiaobaigao.ai.pojo.*;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;


@Mapper
public interface productMapper {
    @Select("SELECT name,count,img FROM product LIMIT #{start},#{pageSize}")
    List<PageInformation> show(Integer start, Integer pageSize);

    @Select("SELECT name,count,img FROM product ORDER BY count DESC LIMIT #{count}")
    List<PageInformation> normalRecommand(Integer count);

    @Select("SELECT name FROM product ORDER BY RAND() LIMIT #{count}")
    List<String> normalRecommandExpand (Integer count);

    @Insert("INSERT INTO product (name, count, img, msg, category) " +
            "VALUES (#{name}, #{count}, #{img}, #{msg}, #{category})")
    void insert(Product product);

    @Update("UPDATE product SET count = count+#{count} WHERE name = #{name}")
    void add(Integer count,String name);

    @Select("SELECT count(*) FROM product")
    Integer getCount();

    @Update("UPDATE product " +
            "SET count = IF(count > #{count}, count - #{count}, 0), " +
            "    category = IF(count=0, -1, category) " +
            "WHERE name = #{name}")
    Integer deletByName(String name,Integer count);

    @Select("SELECT product.category FROM product WHERE id = #{id} OR name = #{name}")
    Integer findCategoryByName(String name);

    @Select("SELECT img FROM product WHERE name=#{name}")
    String getImgByName(String name);

    @Select("SELECT tag1, tag2, tag3 FROM tag WHERE name = #{name}")
    Tags getTags(String name);

    @Select("SELECT sum(count) FROM product")
    int getAllCount();

    @Select("SELECT count FROM product WHERE name = #{name}")
    Integer getLocalCount(String name);

    @Select("SELECT msg,count FROM product WHERE name = #{name}")
    Map<String, Integer> getMsgAndCount(String name);

    @Select("SELECT t.name FROM (SELECT tag.name, (CASE WHEN tag.tag1 IS NOT NULL AND tag.tag1 = base.tag1 THEN 1 ELSE 0 END) + (CASE WHEN tag.tag2 IS NOT NULL AND tag.tag2 = base.tag2 THEN 1 ELSE 0 END) + (CASE WHEN tag.tag3 IS NOT NULL AND tag.tag3 = base.tag3 THEN 1 ELSE 0 END) AS match_count FROM tag, (SELECT tag1, tag2, tag3 FROM tag WHERE name = #{name}) AS base ) AS t order by match_count desc limit #{count};")
    List<String> findBySimilarTag(String name, Integer count);

    @Select("SELECT t.NAME FROM ( SELECT tag.NAME, ( CASE WHEN tag.tag1 IS NOT NULL AND tag.tag1 = base.tag1 THEN 1 ELSE 0 END ) + ( CASE WHEN tag.tag2 IS NOT NULL AND tag.tag2 = base.tag2 THEN 1 ELSE 0 END ) + ( CASE WHEN tag.tag3 IS NOT NULL AND tag.tag3 = base.tag3 THEN 1 ELSE 0 END ) AS match_count FROM tag, ( SELECT tag1, tag2, tag3  FROM tag  WHERE NAME = #{name}) AS base ) AS t order by match_count desc limit 15;")
    List<String> recommandProduct(String name);

    @Update("UPDATE user SET authorization = #{authorization} WHERE user_name = #{name}")
    void setAuthorization(String authorization,String name);

    @Update("UPDATE user SET app_id = #{appId} WHERE user_name = #{name}")
    void setAgent(String appId, String name);

    @Select("SELECT user.authorization,user.app_id FROM user WHERE user_name = #{userName}")
    UserResult findInformations(String userName);

    @Select("SELECT name FROM product")
    List<String> selectNames ();

    @Select("SELECT name,count,img FROM product WHERE name = #{name}")
    PageInformation getPageInformation(String name);

    @Select("SELECT category FROM product where name = #{name}")
    Integer getCategory(String name);

    @Select("SELECT * FROM product where name = #{name}")
    Product getProductByName(String name);

    @Delete("DELETE FROM product where name = #{name}")
    void deleteProductByName(String name);

    @Delete("DELETE FROM tag where name = #{name}")
    void deleteTagsByName(String name);

    @Select("SELECT id from product where name = #{name}")
    Integer getProductIdByName(String name);

    @Insert("INSERT INTO tag (name, tag1, tag2, tag3) VALUES (#{name},#{tag1},#{tag2},#{tag3})")
    void insertTags(String name, String tag1, String tag2, String tag3);

    @Insert("INSERT INTO product_size (name, length, width, height) values (#{name},#{length},#{width},#{height})")
    void insertSize(String name,Integer length, Integer width, Integer height);

    @Select("SELECT * FROM product_size WHERE name = #{name}")
    ProductSize getProductSizeByName(String name);

    @Select("SELECT volume,remain FROM tableforcgy")
    List<TableForcgy> getTableForcgyByName();

    @Update("UPDATE tableforcgy set remain = #{remain} WHERE category = #{category}")
    void setTableForcgyByName(Double remain, Integer category);

    @Delete("DELETE FROM product_size WHERE name = #{name}")
    void deleteSizeByName(String name);

    @Update("UPDATE product set category = #{category} where name =#{name}")
    void setCategoryByName(String name,Integer category);
}
